package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import ch.zbw.pra.sysventory.database.mapper.HardDiskMapper;
import ch.zbw.pra.sysventory.model.HardDisk;

import com.mysql.jdbc.Statement;

public class HardDiskJDBCTemplate implements Serializable {

	/**
	 * Greift auf die Datenbank zu (DB-Tabelle HardDisk) und delegiert das Mapping der Tabellenspalten an den RowMapper
	 * Klasse kann auf die Datenbank schreiben und lesen
	 * 
	 * @author Ralph Koster
	 */
	private static final long serialVersionUID = 5111063958006151537L;
	private static Logger LOG = LoggerFactory.getLogger(HardDiskJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	private static final String INSERT_SQL = "INSERT INTO HardDisk (hardDriveLetter, hardCapacity, hardFreeSpace, hardEfficiency, hard_compID_fk) VALUES(?,?,?,?,?)";
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}

	public int insertHardDisk(final HardDisk hardDisk, final Integer computerId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, hardDisk.getDriveLetter()); 
					ps.setString(2, hardDisk.getHardDiskCapacity()); 
					ps.setString(3, hardDisk.getHardDiskFreeSpace());
					ps.setString(4, hardDisk.getHardDiskEfficiency());
					ps.setString(5, String.valueOf(computerId)); 
					return ps; 
				} 
			}, keyHolder);
		} catch(DataAccessException e){
			LOG.debug("Failed to insert HardDisk for ComputerId "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Inserted hardDisk with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}

	public List<HardDisk> listHardDisks() {
		String SQL = "SELECT * FROM HardDisk";
		List<HardDisk> hardDisks = new ArrayList<HardDisk>();
		try{
			hardDisks = jdbcTemplateObject.query(SQL,
					new HardDiskMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to load a list of HardDisks");
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of HardDisks");
		return hardDisks;
	}

	public List<HardDisk> listHardDisksFromComputer(int computerId) {
		String SQL = "SELECT * FROM HardDisk WHERE hard_compID_fk = ?";
		List<HardDisk> hardDisks = new ArrayList<HardDisk>();
		try{
			hardDisks = jdbcTemplateObject.query(SQL,
				new Object[] { computerId }, new HardDiskMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to load a List of HardDisks for ComputerID "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a a list of HardDisks for ComputerId "+computerId);
		return hardDisks;
	}
}
